<?php
include("dbinfo.inc.php");

mysql_connect(localhost,$username,$password);

$con = mysql_connect(localhost,$username,$password);

// Make the Trail database
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

if (mysql_query("CREATE DATABASE $database",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

@mysql_select_db($database) or die( "Unable to select database in setup.php");

/*
--IDs can be a max of 6 integers
--tinytext is a string w/ max length of 255 chars
--double(9,6) means you have a double with max 9 digits, with 6 of those digits
  to the right of the decimal.
--you can enter either yes or no for dog and bike. if you enter something else
  it is left blank.
*/
$query="
    CREATE TABLE TrailHead (
    ID int(6) NOT NULL auto_increment,
    name varchar(255) NOT NULL,
    gps varchar(255) NOT NULL,
    start_altitude int(5),
    dog enum('yes','no','both'),
    bike enum('yes','no','both'),
    PRIMARY KEY (ID),
    UNIQUE (name)
);";
if (!mysql_query($query)) {
  echo "Error creating TrailHead Table: " . mysql_error();
}

/*
--difficulty values range from 0 to 10
--the text data type is a string w/ max length of 65,535 chars
*/
$query="
CREATE TABLE Trail (
    ID int(6) NOT NULL auto_increment,
    headID int(6) NOT NULL,
    name varchar(255) NOT NULL,
    length double(9,4),
    highest_altitude int(5),
    difficulty int(2),
    avgRating double(4,2),
    notes text,
    PRIMARY KEY (ID),
    CONSTRAINT trailfk_head FOREIGN KEY (headID) REFERENCES TrailHead(ID),
    UNIQUE (name),
    CHECK (difficulty > -1),
    CHECK (difficulty < 11),
    CHECK (avgRating >= 0),
    CHECK (avgRating <= 10)
);";
if (!mysql_query($query)) {
  echo "Error creating Trail Table: " . mysql_error();
}

$query="
CREATE TABLE Account (
    ID int(6) NOT NULL auto_increment,
    userName varchar(30) NOT NULL,
    password varchar(32) NOT NULL,
    firstName varchar(30) NOT NULL,
    lastName varchar(30) NOT NULL,
    admin enum('yes', 'no') NOT NULL,
    city varchar(30),
    state varchar(30),
    DOB date,
    sex enum('m','f'),
    bio text,
    photo varchar(60),
    PRIMARY KEY (ID),
    UNIQUE (userName)
)";
if (!mysql_query($query)) {
  echo "Error creating Accounts Table: " . mysql_error();
}

/*
--rating values range from 0 to 10
--each user can only write one review per trail
*/
$query="
CREATE TABLE Review (
    ID int(6) NOT NULL auto_increment,
    trailID int(6) NOT NULL,
    userID int(6) NOT NULL,
    rating int(2) NOT NULL,
    review text,
    PRIMARY KEY (ID),
    CONSTRAINT reviewfk_trail FOREIGN KEY (trailID) REFERENCES Trail(ID),
    CONSTRAINT reviewfk_user FOREIGN KEY (userID) REFERENCES Account(ID),
    UNIQUE (trailID, userID),
    CHECK (rating > -1),
    CHECK (rating < 11)
);";
if (!mysql_query($query)) {
  echo "Error creating Review Table: " . mysql_error();
}

/*
--all three columns are foreign keys
--the combination of trailID and userID must be unique
*/
$query="
CREATE TABLE Saved (
    ID int(6) NOT NULL auto_increment,
    headID int(6) NOT NULL,
    trailID int(6) NOT NULL,
    userID int(6) NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT savedfk_head FOREIGN KEY (headID) REFERENCES TrailHead(ID),
    CONSTRAINT savedfk_trail FOREIGN KEY (trailID) REFERENCES Trail(ID),
    CONSTRAINT savedfk_acct FOREIGN KEY (userID) REFERENCES Account(ID),
    UNIQUE (trailID, userID)
)";
if (!mysql_query($query)) {
  echo "Error creating Saved Table: " . mysql_error();
}

$query="
CREATE TABLE Filter (
    ID int(6) NOT NULL auto_increment,
    userID int(6) NOT NULL,
    start_altitude int(5),
    dog enum('yes','no','both'),
    bike enum('yes','no','both'),
    length double(9,4),
    highest_altitude int(5),
    difficulty int(2),
    PRIMARY KEY (ID),
    CONSTRAINT filterfk_acct FOREIGN KEY (userID) REFERENCES Account(ID)
)";
if (!mysql_query($query)) {
  echo "Error creating Filters Table: " . mysql_error();
}

/*
$query="
CREATE TRIGGER insAcctTrig AFTER INSERT ON Accounts
    FOR EACH ROW BEGIN
	set @stmt1 = CONCAT('CREATE TABLE ', new.userName, 
	'Filter (
	    ID int(6) NOT NULL auto_increment,
	    start_altitude int(5),
	    dog enum(\'yes\',\'no\'),
	    big enum(\'yes\',\'no\'),
	    length double(9,4),
	    highest_altitude int(5),
	    difficulty int(2),
	    PRIMARY KEY (ID)
	);'
	);
	Prepare st1 from @stmt1;
	Execute st1;
	Deallocate prepare st1;

	set @stmt2 = CONCAT('CREATE TABLE ', new.userName, 
	'Trails (
	    ID int(6) NOT NULL auto_increment,
	    trailID int(6) NOT NULL,
	    headID int(6),
	    PRIMARY KEY (ID),
	    UNIQUE (trailID)
	);'
	);
	Prepare st2 from @stmt2;
	Execute st2;
	Deallocate prepare st2;
    END;
";
if (!mysql_query($query)) {
  echo "Error creating insert Account trigger: " . mysql_error();
}
*/

// insert test accounts, the first is an admin
$query="
INSERT INTO Account (userName, password, firstName, lastName, admin, city, state, DOB, sex, bio, photo)
VALUES ('testuser', MD5('password'), 'Test', 'User', 'yes', 'Boulder', 'Colorado','2000-01-20', 'm', 'Easily Irratated', 'hoodie-red-cat.jpg')
";
if (!mysql_query($query)) {
  echo "Error inserting test account: " . mysql_error();
}
$query="
INSERT INTO Account (userName, password, firstName, lastName, admin, city, state, DOB, sex, bio, photo)
VALUES ('testuser2', MD5('password'), 'Test', 'User', 'no', 'Boulder', 'Colorado','2000-01-20', 'm', 'Easily Irratated', 'husky.jpg')
";
if (!mysql_query($query)) {
  echo "Error inserting test account: " . mysql_error();
}

// insert two trailhead entries
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Boulder Falls Trailhead', 'N40 00.274 W105 24.374', 6965,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Chautauqua Park Trailhead', 'N39 59.930 W105 16.970', 5710,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Coot Lake', 'N40 04.290 W105 14.094', 5204,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Eagle Trailhead', 'N40 04.813 W105 14.162', 5260,'yes','yes')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Button Rock Preserve Trailhead', 'N40 13.707 W105 20.582', 6020,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Doudy Draw Trailhead', 'N39 56.279 W105 15.392', 5670,'no','yes')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Eldorado Canyon Trailhead', 'N39 55.845 W105 17.654', 6075,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Eldorado Mountain Trailhead', 'N39 55.904 W105 16.228', 5885,'yes','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Heil Valley Ranch - Main Trailhead', 'N40 08.958 W105 18.009', 5945,'no','no')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}
$query="
INSERT INTO TrailHead (name, gps, start_altitude, dog, bike)
VALUES ('Heil Valley Ranch - Picture Rock Trailhead', 'N40 12.682 W105 16.351', 5415,'no','yes')";
if (!mysql_query($query)) {
  echo "Error inserting trail head: " . mysql_error();
}

// insert two trail entries
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (1, 'Boulder Falls', .3, 7075, 1, 'Visitors are required to stay on the main path that ends at a viewpoint just below the falls.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'Bear Peak via Mesa Trail and Bear Canyon', 12.7, 8461, 7, 'There are seasonal closures for wildlife activity and parking is very limited.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'Flatiron #1', 2.9, 7132, 7, 'The trail\'s steep grades and rugged terrain can be challenging with lingering ice and snow. Sturdy footwear and poles are recommended when such conditions exist.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'Green Mountain Loop', 8.75, 8144, 7, 'Bears and other wildlife rely heavily on fruits and berries in Bear Canyon and Gregory Canyon. Avoid going off-trail, and leave fruits and berries to the local wildlife.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'Mallory Cave', 5.4, 6825, 5, 'Honor seasonal closures and do not disturb roosting bat colonies. Entering Mallory Cave when closed is punishable by death.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'Royal Arch', 3.2, 6915, 7, 'The Royal Arch Trail is a great place to observe climbers on the Flatirons.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (2, 'The Mesa Trail', 13.4, 6465, 7, 'Area wildlife includes deer, coyote, fox, bear, mountain lion, bobcat, bats, rattlesnakes and numerous raptor species. Be cognizant of wildlife activity updates, warnings and resulting trail closures or usage limitations.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (3, 'Coot Lake Trail', 1.2, 5235, 1, 'Coot Lake and its adjacent marshes are sensitive wetland habitants. Enjoy wildlife from a distance and honor seasonal closures for nesting waterfowl.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (4, 'Eagle Trail - Sage Trail loop', 3.75, 5371, 2, 'The Eagle-Sage Trail\'s wide track and mild grads are well-suited for cross-country skiing.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (4, 'North Rim Trail', 1.0, 5320, 1, 'Accessed from the Sage Trail. Leads to north to Neva Road.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (5, 'Ralph Price Reservoir', 3.8, 6439, 4, 'The Ralph Price Reservoir is a regularly patrolled area. Dog leash and control laws are strictly enforced. There is NO mountain biking or camping permitted in the Preserve.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (5, 'Sleepy Lion Trail - Button Rock Dam Loop', 4.7, 6625, 6, 'The Sleepy Lion Trail draws its name from a ranger who once observed a mountain lion sleeping on a rock along a trail.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (6, 'Doudy Draw - Spring Brook Loop Trail', 5.2, 6242, 4, 'Dawn and dusk travelers should be mindful of bear and mountain lion activity in this area.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (6, 'Doudy Draw Trail', 6.8, 6112, 4, 'Wide tracks with mild, groomed grades are well-suited for running and winter recreation.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (7, 'Eldorado Canyon - Walker Ranch Lollipop Loop', 13.8, 7355, 9, 'The Walker Ranch Loop is favored by local trail runners and mountain bikers; be prepared to share the trail.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (7, 'Eldorado Canyon Trail', 8.5, 7090, 7, 'There is a fee to enter Eldorado Canyon State Park. It ranges between $5 and $7, depending on the season. Do not park in the town of Eldorado Springs, as you will be ticketed and towed.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (7, 'Walker Ranch Loop', 7.55, 7355, 5, 'Good fishing and picnic locales can be found along South Boulder Creek.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (7, 'Rattlesnake Gulch Trail', 3.8, 7080, 5, 'Before setting out, consider stopping by the Visitor Center for a free brochure explaining the canyon\'s complex geology and rich biology.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (7, 'South Boulder Creek Trail', 5.0, 7287, 5, 'Black Bear and Mountain Lion inhabit this area. Be responsible with trash, pets and children.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (8, 'Fowler Trail', 4.0, 6117, 2, 'Dogs must be leashed within Eldorado Canyon State Park boundaries.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (8, 'Goshawk Ridge Trail - Spring Brook North Loop', 4.05, 6605, 5, 'The Goshawk Ridge Trail is aptly named for the Northern Goshawk, a medium-large species distinguished by searing yellow-red eyes, yellow talons and their preferred habitat of tall, contiguous tracts of coniferous forest.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (9, 'Lichen Loop Trail', 1.25, 6175, 2, 'Near the loop\'s end you may notice an unusual structure on the hillside above. This beehive-like structure, constructed of Lyons Sandstone blocks, is a lime kiln. In the late 1800s lime was a common ingredient for mortar and cement, used as a soil enhancement and to wash buildings.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (9, 'Wapiti Trail to Ponderosa Loop', 7.7, 6792, 5, 'Heil Ranch\'s mild grades and well-defined trails are well-suited for families and winter activities.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (9, 'Wild Turkey Loop', 9.45, 6705, 5, 'The prairie dog village described above was wiped out in 2008 by disease; though unfortunate, occasional collapses are part of the prairie dog life cycle, and this village will likely repopulate in years to come.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
$query="
INSERT INTO Trail (headID, name, length, highest_altitude, difficulty, notes)
VALUES (10, 'Picture Rock Trail', 10.4, 6392, 5, 'Heil Valley Ranch supports one of the last elk herds that venture from the Continental Divide to their natural winter grounds on the eastern plains.')";
if (!mysql_query($query)) {
  echo "Error inserting trail: " . mysql_error();
}
// insert two review entries
$query="
INSERT INTO Review (trailID, userID, rating, review)
VALUES (1, 1, 2, 'Bla bla bla, real pretty')";
if (!mysql_query($query)) {
  echo "Error inserting review: " . mysql_error();
}
$query="
INSERT INTO Review (trailID, userID, rating, review)
VALUES (2, 2, 10, 'OMG OMG OMG, real pretty')";
if (!mysql_query($query)) {
  echo "Error inserting review: " . mysql_error();
}
$query="
INSERT INTO Saved (headID, trailID, userID)
VALUES (1,1,1)";
if(!mysql_query($query)){
  echo "Error inserting saved trail: " . mysql_error();
}
$query="
INSERT INTO Saved (headID, trailID, userID)
VALUES (2,2,1)";
if(!mysql_query($query)){
  echo "Error inserting saved trail: " . mysql_error();
}
$query="
INSERT INTO Saved (headID, trailID, userID)
VALUES (2,3,1)";
if(!mysql_query($query)){
  echo "Error inserting saved trail: " . mysql_error();
}
$query="
INSERT INTO Saved (headID, trailID, userID)
VALUES (3,8,1)";
if(!mysql_query($query)){
  echo "Error inserting saved trail: " . mysql_error();
}
mysql_close(); 
echo " and populated";
?>
